package flights.db;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import java.sql.PreparedStatement;

import flights.bi.Flight;

public class FlightHandler 
{
	public static List<Flight> getAllFlights() throws SQLException
	{
		List<Flight> Flights = new ArrayList<Flight>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM flight";
		
		try (Statement st = conn.createStatement()) {
			ResultSet rs = st.executeQuery(sql);
			Flights = extractFlightsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return Flights;
	}
	
	public static List<Flight> getAllFlightsByAirline(String airline) throws SQLException
	{
		List<Flight> Flights = new ArrayList<Flight>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT f.* FROM flight f JOIN airline a on (f.airline_id = a.airline_id) WHERE a.name = ?";
		
		try (PreparedStatement st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
			st.clearParameters();
			st.setString(1, airline);
			ResultSet rs = st.executeQuery(sql);
			Flights = extractFlightsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return Flights;
	}
	
	public static Flight getFlightByID(int nFlightID) throws SQLException
	{
		List<Flight> Flights = new ArrayList<Flight>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM flight where flight_id=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nFlightID);
			ResultSet rs = st.executeQuery();
			Flights = extractFlightsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		if (!Flights.isEmpty())
		{
			return Flights.get(0);
		}
		else
		{
			return null;
		}
	}
	
	public static List<Flight> getFlightByparameters(Integer nBottomCost,Integer nTopCost,String sSourcePort,String sDestPort,Date dStartRange,Date dEndRange)
	throws SQLException
	{
		List<Flight> Flights = new ArrayList<Flight>();
		
		String sql;
		String baseSql = "SELECT distinct f.* FROM flight f ";
		String sqlFromAdditionCost = ", flight_seat fs ";
		String conditionBottomCost = " AND fs.cost >= ? ";
		int    conditionBottomCostPos =0;
		String conditionTopCost = " AND fs.cost <= ? ";
		int    conditionTopCostPos =0;
		String conditionSourcePort = " AND f.Depart_Airport = ? ";
		int    conditionSourcePortPos =0;
		String conditionDestPort = " AND f.Arrival_airport = ? ";
		int    conditionDestPortPos =0;
		String conditionStartRange = " AND f.depart_date >= ? ";
		int    conditionStartRangePos =0;
		String conditionEndRange = " AND f.depart_date <= ? ";
		int    conditionEndRangePos =0;
		
		int conditionCounter = 0;
		
		if (nBottomCost != null ||  nTopCost != null)
		{
			sql = baseSql + sqlFromAdditionCost + " where  f.flight_id = fs.Flight_Id";
		}
		else
		{
			sql = baseSql + " where  1=1 ";
		}
		
		if (nBottomCost != null)
		{
			conditionBottomCostPos = ++conditionCounter;
			sql = sql +conditionBottomCost;
		}
		if (nTopCost != null)
		{
			conditionTopCostPos = ++conditionCounter;
			sql = sql +conditionTopCost;
		}
		if (sSourcePort != null)
		{
			conditionSourcePortPos = ++conditionCounter;
			sql = sql +conditionSourcePort;
		}
		if (sDestPort != null)
		{
			conditionDestPortPos = ++conditionCounter;
			sql = sql +conditionDestPort;
		}
		if (dStartRange != null)
		{
			conditionStartRangePos = ++conditionCounter;
			sql = sql +conditionStartRange;
		}
		if (dEndRange != null)
		{
			conditionEndRangePos = ++conditionCounter;
			sql = sql +conditionEndRange;
		}
		
		Connection conn = DBConn.createConnection();
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			
			st.clearParameters();
			
			if (conditionBottomCostPos>0)
			{
				st.setInt(conditionBottomCostPos,nBottomCost);
			}
			if (conditionTopCostPos>0)
			{
				st.setInt(conditionTopCostPos,nTopCost);
			}
			if (conditionSourcePortPos>0)
			{
				st.setString(conditionSourcePortPos,sSourcePort);
			}
			if (conditionDestPortPos>0)
			{
				st.setString(conditionDestPortPos,sDestPort);
			}
			if (conditionStartRangePos>0)
			{
				st.setDate(conditionStartRangePos,dStartRange);
			}
			if (conditionEndRangePos>0)
			{
				st.setDate(conditionEndRangePos,dEndRange);
			}
			
			ResultSet rs = st.executeQuery();
			Flights = extractFlightsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		
		return Flights;
	}
	
	public static int deleteFlightByID(int nFlightID) throws SQLException
	{
		int nRows = 0;
		nRows += TicketHandler.deleteBTicketByFlight(nFlightID);
		nRows += FlightSeatHandler.deleteFlight_SeatByFlightID(nFlightID);
		nRows += BookingHandler.deleteEmptyBookings();
		
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM flight where flight_id=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nFlightID);
			nRows += st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;
	}
	
	public static int upsertFlight(Flight flight) throws SQLException
	{
		List<Flight> singleFlight = new ArrayList<Flight>();
		singleFlight.add(flight);
		int nRetVal = upsertFlight(singleFlight);
		return nRetVal;
	}
	
	public static int upsertFlight(List<Flight> flights) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		int nRowsAffected = 0;
		for (int i = 0; i < flights.size(); i++) {
			if (getFlightByID(flights.get(i).getFlight_id()) != null)
			{
				String sql = "update flight set airline_id = ? , flight_number = ? , depart_airport = ? , arrival_airport = ? , depart_date = ? , arrive_date = ? where flight_id = ?";
				try (PreparedStatement st = conn.prepareStatement(sql)) {
					st.clearParameters();
					st.setInt(1,flights.get(i).getAirline_id());
					st.setString(2,flights.get(i).getFlight_number());
					st.setString(3, flights.get(i).getDepart_airport());
					st.setString(4, flights.get(i).getArrival_airport());
					st.setDate(5, flights.get(i).getDepart_date());
					st.setDate(6, flights.get(i).getArrive_date());
					st.setInt(7, flights.get(i).getFlight_id());
					nRowsAffected += st.executeUpdate();
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
			}
			else // new flight
			{
				String sql = "insert into flight(airline_id,flight_number,depart_airport,arrival_airport,depart_date,arrive_date) values (?,?,?,?,?,?)";
				try (PreparedStatement st = conn.prepareStatement(sql)) {
					st.clearParameters();
					st.setInt(1,flights.get(i).getAirline_id());
					st.setString(2,flights.get(i).getFlight_number());
					st.setString(3, flights.get(i).getDepart_airport());
					st.setString(4, flights.get(i).getArrival_airport());
					st.setDate(5, flights.get(i).getDepart_date());
					st.setDate(6, flights.get(i).getArrive_date());
					nRowsAffected += st.executeUpdate();
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
			}
		}
		
		conn.close();
		return nRowsAffected;
	}
	
	private static List<Flight> extractFlightsFromRS(ResultSet resultSet) throws SQLException {
		List<Flight> flights = new ArrayList<Flight>();
		
		while (resultSet.next()) {
			int flight_id=resultSet.getInt("flight_id");
			int airline_id=resultSet.getInt("airline_id");
			String flight_number=resultSet.getString("flight_number");
			String depart_airport=resultSet.getString("depart_airport");
			String arrival_airport=resultSet.getString ("arrival_airport");
			Date depart_date=resultSet.getDate("depart_date");
			Date arrive_date=resultSet.getDate("arrive_date");
			
			Flight flight = new Flight(flight_id, airline_id, flight_number, depart_airport, arrival_airport, depart_date, arrive_date);
			flights.add(flight);
		}
		
		return flights;
	}

}
